package cn.com.lms.business.questionnaire.persistence.repository;

import cn.com.lms.business.questionnaire.persistence.entity.UserQuestionnaire;
import com.bnzj.core.persistence.repository.BaseRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @Author: 16266
 * @DateTime: 2020/3/24 13:57
 * @Description:
 */
public interface UserQuestionnaireRepository extends BaseRepository<UserQuestionnaire, Long> {

    /**
     * 获取课程问卷题目平均评分列表
     *
     * @param courseId
     * @return
     * @author zhangzhenjiang
     */
    @Query(value = " select questionnaire_sub_id as questionnaireSubId,s.questionnaire_top as questionnaireTop, s.questionnaire_info_id as  questionnaireInfoId," +
            " i.questionnaire_name as questionnaireName,sum(questionnaire_opt_scr)/count(1) as  avgScore  from bnz_user_questionnaire_opt uqo" +
            " LEFT JOIN bnz_questionnaire_sub s on s.id=questionnaire_sub_id" +
            " LEFT JOIN bnz_questionnaire_info i on i.id=s.questionnaire_info_id " +
            " LEFT JOIN bnz_user_questionnaire uq on uq.id=uqo.user_questionnaire_id " +
            " LEFT JOIN bnz_learning_stage_detail sd on sd.id=uq.stage_detail_id " +
            " where  questionnaire_sub_id in (select id from bnz_questionnaire_sub where questionnaire_info_id in (select obj_id from bnz_learning_stage_detail sd LEFT JOIN bnz_questionnaire_info  qi on qi.id=sd.obj_id where qi.is_delete=0 and  study_type=1 and  plan_id in(" +
            " select plan_id from  bnz_learning_stage_detail where obj_id=?1 and  study_type=0 GROUP BY plan_id) GROUP BY obj_id)) " +
            "  and uq.stage_detail_id in (select sd.id from bnz_learning_stage_detail sd LEFT JOIN bnz_questionnaire_info  qi on qi.id=sd.obj_id where qi.is_delete=0 and  study_type=1 and  plan_id in (" +
            " select plan_id from  bnz_learning_stage_detail where obj_id=?1 and study_type=0 GROUP BY plan_id" +
            ") )" +
            "GROUP BY questionnaire_sub_id", nativeQuery = true)
    List<Map<String, Object>> getQuestionnaireSubjectAvgScoreByCourseId(Long courseId);

    @Query(value = " select sum(tem.avg_score)/13 as score from (" +
            "select questionnaire_sub_id,s.questionnaire_top,s.questionnaire_info_id,i.questionnaire_name, sum(questionnaire_opt_scr)/count(1) avg_score  from bnz_user_questionnaire_opt uqo LEFT JOIN bnz_questionnaire_sub s on s.id=questionnaire_sub_id " +
            " LEFT JOIN bnz_questionnaire_info i on i.id=s.questionnaire_info_id " +
            " LEFT JOIN bnz_user_questionnaire uq on uq.id=uqo.user_questionnaire_id " +
            " LEFT JOIN bnz_learning_stage_detail sd on sd.id=uq.stage_detail_id " +
            "where questionnaire_sub_id in (select id from bnz_questionnaire_sub where questionnaire_info_id in (" +
            "select obj_id from bnz_learning_stage_detail sd LEFT JOIN bnz_questionnaire_info  qi on qi.id=sd.obj_id where qi.is_delete=0 and  study_type=1 and  plan_id in (" +
            "select plan_id from  bnz_learning_stage_detail " +
            "where obj_id=?1 and  study_type=0 GROUP BY plan_id) GROUP BY obj_id" +
            ") ) " +
            "  and uq.stage_detail_id in (select sd.id from bnz_learning_stage_detail sd LEFT JOIN bnz_questionnaire_info  qi on qi.id=sd.obj_id where qi.is_delete=0 and  study_type=1 and  plan_id in (" +
            " select plan_id from  bnz_learning_stage_detail where obj_id=?1 and study_type=0 GROUP BY plan_id" +
            ") )" +
            "GROUP BY questionnaire_sub_id) tem", nativeQuery = true)
    Map<String, Object> getTotalQuestionnaireSubjectAvgScoreByCourseId(Long courseId);


    /*
     * @author liuwei
     * @date 2020/4/27 10:15
     * @desc 查询用户的问卷调查结果
     */
    @Query(value = "select  qs.id quesId,qs.questionnaire_top questionnaireTop,qs.questionnaire_type questionnaireType,qso.questionnaire_opt as questionnaireOpt,qso.id as qsoId ," +
            "qso.questionnaire_sub_id questionnaireSubId,a.* from bnz_questionnaire_sub qs left join bnz_questionnaire_sub_opt qso on qs.id=qso.questionnaire_sub_id" +
            " left join (select qo.questionnaire_sub_id answerSubId, qo.questionnaire_sub_opt_id answerId,qo.questionnaire_opt answerDesc" +
            " from bnz_user_questionnaire  us left join bnz_user_questionnaire_opt qo on qo.user_questionnaire_id=us.id" +
            " where  stage_detail_id=?1 and user_id=?2 ) a on a.answerSubId=qs.id where qs.questionnaire_info_id=?3", nativeQuery = true)
    List<Map<String, Object>> getStageUserQuestion(Long stageDetailId, Long userId, Long questionInfoId);


}
